import pymysql
from pymysql import err


class personDao:
    def __init__(self):
        host = "localhost"  # 主机名
        port = 3306  # 端口号
        username = "root"  # 用户名
        password = "206369"  # 密码
        database = "go_db"  # 数据库名
        self.db = pymysql.connect(host=host, port=port, user=username, password=password,
                                  database=database)  # 数据库的连接
        self.cursor = self.db.cursor(cursor=pymysql.cursors.DictCursor)  # 设置游标

    # 查询语句
    def queryPerson(self, sql, params):
        try:
            self.cursor.execute(sql, params)
            result = self.cursor.fetchall()
            return result
        except err:
            print(err)
            self.db.close()
            return None

    # 插入数据
    def insertPerson(self, sql, values):
        try:
            self.cursor.executemany(sql, values)
            self.db.commit()
            print("插入了", self.cursor.rowcount, "条数据")
            return self.cursor.rowcount
        except err:
            print(err)
            self.db.rollback()  # 发生了错误，事务回滚
            return 0

    # 删除数据
    def deletePerson(self, sql, params):
        try:
            self.cursor.executemany(sql, params)
            self.db.commit()  # 事务提交
            print("删除了", self.cursor.rowcount, "条数据")
            return self.cursor.rowcount
        except err:
            print(err)
            self.db.rollback()  # 事务回滚
            return 0

    # 更新数据
    def updatePerson(self, sql, params):
        try:
            self.cursor.execute(sql, params)
            self.db.commit()  # 事务提交
            print("更新了", self.cursor.rowcount, "条数据")
            return self.cursor.rowcount
        except err:
            print(err)
            self.db.rollback()  # 事务回滚
            return 0
